import os
from pathlib import Path
import sqlite3
from ttkbootstrap import Image
from tkinter.messagebox import showerror, showinfo
from tkinter.filedialog import askopenfile, askdirectory
from log import log
import traceback

# global variables
# region 全局变量

IMG_PATH = Path(__file__).parent.parent / "assets"
DB_PATH = Path(__file__).parent.parent / "db"

GROUP_IMAGE_SIZE = 40
GOOD_IMAGE_SIZE = 30
TREEVIEW_ROW_HEIGHT = 40
TREEVIEW_GROUP_FONT_SIZE = 18
TREEVIEW_GROUP_FONT_STYLE = "bold"
TREEVIEW_GROUP_FONT_FAMILY = ""
DEFAULT_GROUP_IMAGE = IMG_PATH / "default_group_image.png"
DEFAULT_GOOD_IMAGE = IMG_PATH / "default_good_image.png"
GROUP_NAME_MAX_LEN = 30
GOOD_NAME_MAX_LEN = 30
BUTTON_IMAGE_SIZE = 20

APP_ICON = IMG_PATH / "good-manage.png"

THEME = "cosmo"

ROOT_WINDOW = None
GOODS_TREE_FRAME = None
GOODS_TREE = None
INDEX = None
SETTING = None
ORDER = None
logger = log("./GoodManager.log")


# endregion

# utils
# 按照图片的比例，缩放，放大图片
# def set_image_size(image, size):
#     if not isinstance(image, PIL.PngImagePlugin.PngImageFile):
#         raise RuntimeError("image is not a PIL.PngImagePlugin.PngImageFile")
#     x = image.size[0]
#     y = image.size[1]
#     if x > y or x == y:
#         new_image = image.resize((size, int(size / (x / y))))
#     else:
#         new_image = image.resize((int(size / (y / x)), size))
#     return new_image

# 按照图片的比例，缩放，放大图片


def set_image_size(image_path, size=None):
    try:
        image = Image.open(image_path)
        if size is None:
            return image
        x = image.size[0]
        y = image.size[1]
        if x > y or x == y:
            image = image.resize((size, int(size / (x / y))), Image.ANTIALIAS)
        else:
            image = image.resize((int(size / (y / x)), size), Image.ANTIALIAS)
        return image
    except Exception as e:
        showerror("错误", f"图片无法打开。\n{e}")


def select_file(file):
    try:
        folder = askopenfile()
        if folder:
            file.set(folder.name)
    except Exception as e:
        showerror("错误", "打开文件错误。")
        logger.error(e)


def select_folder(file):
    try:
        folder = askdirectory()
        if folder:
            file.set(folder)
    except Exception as e:
        showerror("错误", "打开文件夹错误。")
        logger.error(e)


def verify_image(image_path):
    try:
        Image.open(image_path)
    except Exception as e:
        showerror("错误", f"图片无法打开。\n{e}")


def open_pdf(order_group_id):
    try:
        # 获取最近订单的存放路径
        order_path = get_order_save_path_by_order_group_id(order_group_id)
        if not order_path:
            showerror("错误", f"订单路径获取失败。")
            return False
        # 指定应用打开
        # 获取应用路径（需要用户自行配置）
        app_path = get_app_path()
        if not app_path:
            showerror("错误", f"默认应用路径获取失败。")
            return False
        # windows
        # cmd = f"{app_path} {order_path}"
        # mac
        cmd = f"open -a {app_path} {order_path}"
        os.system(cmd)
        return True
    except Exception as e:
        showerror("错误", f"试图打开订单PDF文件时出错。\n{e}")
        logger.error(traceback.print_exc())
        logger.error(e)
        return False


def remove_single_pdf(order_group_id: int):
    try:
        pdf_path = get_order_save_path_by_order_group_id(order_group_id)
        # 先判断一下路径是否是个pdf文件，如果不是提示用户
        if not pdf_path.endswith(".pdf"):
            showerror("错误", "此订单pdf文件路径错误。")
            return False
        # mac: rm -f pdf_path
        # windows: del pdf_path
        # windows_cmd = f"del {pdf_path}"
        mac_linux_cmd = f"rm -f {pdf_path}"
        if not os.system(mac_linux_cmd) == 0:
            showerror("错误", f"试图删除订单的PDF文件时候出错。\n{mac_linux_cmd}")
            return False
        return True
    except Exception as e:
        showerror("错误", f"试图删除订单的PDF文件时候出错。\n{e}")
        logger.error(traceback.print_exc())
        logger.error(e)
        return False


def remove_multiple_pdf(pdf_path: str):
    try:
        # 先判断一下路径是否是个pdf文件，如果不是提示用户
        if not pdf_path.endswith(".pdf"):
            showerror("错误", "此订单pdf文件路径错误。")
            return False
        # mac: rm -f pdf_path
        # windows: del pdf_path
        # windows_cmd = f"del {pdf_path}"
        mac_linux_cmd = f"rm -f {pdf_path}"
        if not os.system(mac_linux_cmd) == 0:
            # showerror("错误", f"试图删除订单的PDF文件时候出错。\n{mac_linux_cmd}")
            return False
        return True
    except Exception as e:
        # showerror("错误", f"试图删除订单的PDF文件时候出错。\n{e}")
        logger.error(traceback.print_exc())
        logger.error(e)
        return False


# DB
# 获取数据库连接
def get_db_connect():
    return sqlite3.connect(database=DB_PATH / "OrderDB.db")


# if __name__ == '__main__':
#     con = get_db_connect()
#     cursor = con.cursor()
#     sql = """
#     select * from goods where group_name='椅子'
#     """
#     result = cursor.execute(sql).fetchall()
#     logger.info(result)

# 释放数据库游标，连接
def free_db_connection(cursor_, conn):
    try:
        cursor_.close()
    except Exception as e:
        print(e)
    try:
        conn.close()
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)


def get_all_groups():
    cmd = '''
    SELECT group_id,group_name, group_image FROM goods_groups;
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchall()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
    finally:
        free_db_connection(cursor, con)


def get_goods_from_group_name(group_name):
    cmd = f'''
    SELECT goods_id,group_name,goods_name,price,goods_image
    FROM goods AS A 
    WHERE 
    A.group_name = '{group_name}';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchall()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图用组名获取货物信息的时候出错。\n{e}")
    finally:
        free_db_connection(cursor, con)


def get_goods_name_id_from_group_name(group_name):
    cmd = f'''
        SELECT goods_id, goods_name
        FROM goods AS A 
        WHERE 
        A.group_name = '{group_name}';
        '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchall()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图用组名获取货物的时候出错。\n{e}")
    finally:
        free_db_connection(cursor, con)


def get_goods_from_id(goods_id):
    cmd = f'''
    SELECT goods_id,group_name,goods_name,price,goods_image
    FROM goods AS A 
    WHERE A.goods_id = {goods_id};
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchone()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图用货物ID获取货物的时候出错。\n{e}")
        return False
    finally:
        free_db_connection(cursor, con)


def get_group_from_name(group_name):
    cmd = f'''
    SELECT group_id, group_name, group_image
    FROM goods_groups AS A
    WHERE A.group_name = '{group_name}';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchone()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图用货物名字获取货物的时候出错。\n{e}")
        return False
    finally:
        free_db_connection(cursor, con)


def get_group_from_id(group_id):
    cmd = f'''
    SELECT group_id, group_name, group_image
    FROM goods_groups AS A
    WHERE A.group_id = '{group_id}';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchone()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图用ID获取组信息的时候出错\n{e}")
    finally:
        free_db_connection(cursor, con)


def get_welcome_image():
    cmd = f'''
    SELECT value
    from config AS A
    WHERE A.arg = 'welcome_image';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchone()[0]
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取照片墙的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def select_group_by_name(name):
    cmd = f'''
    SELECT group_id, group_name, group_image
    FROM goods_groups AS A
    WHERE A.group_name = '{name}';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchone()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取订单信息的时候出错\n{e}。")
        return None
    finally:
        free_db_connection(cursor, con)


def get_orders_by_orders_group_id(orders_group_id: int):
    cmd = f'''
    SELECT good_id, good_name, price, buy_number, sum_price
    FROM orders
    WHERE order_group_id = {orders_group_id};
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchall()
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图按照orders_group_id获取订单的时候出错\n{e}。")
    finally:
        free_db_connection(cursor, con)


def get_order_base_save_path():
    cmd = f'''
    SELECT value
    FROM config
    WHERE arg = 'orders_save_path';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchone()[0]
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取订单存放位置的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def get_orders_group_create_date_by_orders_group_id(orders_group_id: int):
    cmd = f'''
    SELECT create_date
    FROM orders_group
    WHERE order_group_id = {orders_group_id};
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchone()[0]
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取订单创建时间的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def get_order_save_path_by_order_group_id(orders_group_id: int):
    cmd = f'''
    SELECT order_path
    FROM orders_group
    WHERE order_group_id = {orders_group_id}
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchone()[0]
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取订单存储路径的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def get_all_order_group():
    cmd = f'''
    SELECT order_group_id, create_date, order_path, remark
    FROM orders_group
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        result = cursor.execute(cmd).fetchall()
        return result
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取所有订单的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def get_app_path():
    cmd = f'''
    SELECT value
    FROM config
    WHERE arg = 'app_path';
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        return cursor.execute(cmd).fetchone()[0]
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图获取默认应用路径的时候出错\n{e}。")
        return False
    finally:
        free_db_connection(cursor, con)


def insert_into_group(group_name, group_image):
    cmd = f'''
    INSERT INTO goods_groups (group_name, group_image)
    VALUES ('{group_name}', '{group_image}');
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图插入新组的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(con, cursor)


def insert_into_good(group_name, good_name, good_price, good_image):
    cmd = f'''
    INSERT
        INTO
        goods (group_name,
        goods_name,
        price,
        goods_image)
     VALUES ('{group_name}',
    '{good_name}',
    '{good_price}',
    '{good_image}');
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图添加货物的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def insert_into_order_group(remark: str):
    cmd = f'''
    INSERT INTO orders_group (remark) VALUES ('{remark}')
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        # 返回当前插入的orders_group的ID
        return cursor.lastrowid
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图添加订单组的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def insert_into_orders(order_group_id: int, goods: list):
    try:
        con = get_db_connect()
        cursor = con.cursor()
        for good in goods:
            cmd = f'''
            INSERT INTO orders (order_group_id, good_id, good_name, price, buy_number, sum_price)
            VALUES ({order_group_id}, {good['good_id']}, '{good['good_name']}', 
                    {good['price']}, {good['buy_number']}, '{good['sum_price']}')
            '''
            cursor.execute(cmd)
        # 全部信息插入没问题后commit
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图添加订单信息的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def update_good_information(good_id, group_name, good_name, good_price, good_image):
    cmd = f'''
    UPDATE goods
    SET group_name='{group_name}',
        goods_name='{good_name}',
        price='{good_price}',
        goods_image='{good_image}'
    WHERE goods_id = {good_id};    
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图添加货物的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def update_group_information(group_id, group_name, group_image):
    cmd = f'''
    UPDATE goods_groups
    SET group_name='{group_name}',
        group_image='{group_image}'
    WHERE group_id = {group_id};
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图添加货物的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def update_config(arg, value):
    cmd = f'''
    UPDATE config
    SET value='{value}'        
    WHERE arg = '{arg}'
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图更改{arg}的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def update_order_path_by_order_group_id(order_path, order_group_id):
    cmd = f'''
    UPDATE orders_group
    SET order_path = '{order_path}'
    WHERE order_group_id = {order_group_id}
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


# 删除组必须得删掉里面的元素
def delete_group_by_name(group_name):
    delete_good = f'''
        DELETE
        FROM goods as A
        WHERE
        A.group_name = '{group_name}';
        '''
    delete_group = f'''
        DELETE
        FROM
            goods_groups as A
        WHERE
            A.group_name = '{group_name}';
        '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        # 删除货物
        cursor.execute(delete_good)
        # 删除组
        cursor.execute(delete_group)
        con.commit()
        showinfo("成功", "删除货物组成功！")
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图删除组的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def delete_good_by_id(good_id):
    cmd = f'''
    DELETE 
    FROM 
        goods as A
    WHERE 
        A.goods_id = {good_id};
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(cmd)
        con.commit()
        showinfo("成功", "货物删除成功！")
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图删除货物的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def delete_order_group_by_order_group_id(order_group_id: int):
    delete_order = f'''
    DELETE 
    FROM orders
    WHERE order_group_id = {order_group_id}
    '''
    delete_order_group = f'''
    DELETE 
    FROM orders_group
    WHERE order_group_id = {order_group_id}
    '''
    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(delete_order)
        cursor.execute(delete_order_group)
        con.commit()
        showinfo("成功", "订单信息删除成功。")
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图删除订单信息的时候出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)


def delete_all_order():
    delete_order_data = '''
    DELETE
    FROM orders;
    '''
    set_id_begin_zero_order = '''
    UPDATE sqlite_sequence
    SET seq=0
    WHERE name = 'orders';
    '''
    delete_order_group_data = '''
    DELETE
    FROM orders_group;
    '''
    set_id_begin_zero_order_group = '''
    UPDATE sqlite_sequence
    SET seq=0
    WHERE name = 'orders_group';
    '''

    try:
        con = get_db_connect()
        cursor = con.cursor()
        cursor.execute(delete_order_data)
        cursor.execute(set_id_begin_zero_order)
        cursor.execute(delete_order_group_data)
        cursor.execute(set_id_begin_zero_order_group)
        con.commit()
        showinfo("成功", "成功删除所有订单记录。")
        return True
    except Exception as e:
        logger.error(traceback.print_exc())
        logger.error(e)
        showerror("错误", f"试图删除所有订单记录出错\n{e}。")
        return False
    finally:
        con.rollback()
        free_db_connection(cursor, con)
